{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95637f19-ea03-4ccf-90d4-b71d323abb27",
   "metadata": {},
   "source": [
    "# Oracle Database\n",
    "\n",
    "This tutorial will show you how to get an Oracle instance up and running locally to test JupySQL. You can run this in a Jupyter notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2148ccd5-8acd-465b-bd56-f769afb6d731",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Pre-requisites\n",
    "\n",
    "To run this tutorial, you need to install following Python packages:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9b37f055-05f6-48a1-9181-858f18184513",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install oracledb --quiet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "103b788b-15fd-4648-83f0-e82674c63693",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Start Oracle instance\n",
    "\n",
    "We use the non-official image [gvenzl/oracle-free](https://hub.docker.com/r/gvenzl/oracle-free) to initial the instance, and database users (this will take 1-2 minutes):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1286fc96-0365-4af5-a595-82adcdcabe8c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cc531fdc8802c40aa666a8b3eb52debda71fc0e64bc00ef956da22314dc9b971\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "docker run --name oracle \\\n",
    "  -e ORACLE_PASSWORD=ploomber_app_admin_password \\\n",
    "  -e APP_USER=ploomber_app \\\n",
    "  -e APP_USER_PASSWORD=ploomber_app_password \\\n",
    "  -p 1521:1521 -d gvenzl/oracle-free"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b41581e7-f25b-45cf-9f05-532d3076b3f0",
   "metadata": {
    "tags": []
   },
   "source": [
    "Our database is running, let’s load some data!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bab85392-8a67-4831-b501-0aee53c8cc2a",
   "metadata": {},
   "source": [
    "## Load sample data\n",
    "\n",
    "Now, let's fetch some sample data. We'll be using the [iris.csv](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c394c8ba-a84c-401e-8ef9-c3d05ed36fc8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(150, 5)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \"https://github.com/Muhd-Shahid/Write-Raw-File-into-Database-Server/raw/main/iris.csv\",  # noqa: E501\n",
    "    index_col=False,\n",
    ")\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f032ed1a-919a-4a20-a122-86c547192c3f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.types import Float\n",
    "\n",
    "engine = create_engine(\n",
    "    \"oracle+oracledb://ploomber_app:ploomber_app_password@localhost:1521/?service_name=FREEPDB1\"  # noqa: E501\n",
    ")\n",
    "df.to_sql(\n",
    "    name=\"iris\",\n",
    "    con=engine,\n",
    "    chunksize=1000,\n",
    "    if_exists=\"replace\",\n",
    "    index=False,\n",
    "    dtype={\n",
    "        \"sepal_length\": Float(),\n",
    "        \"sepal_width\": Float(),\n",
    "        \"petal_length\": Float(),\n",
    "        \"petal_width\": Float(),\n",
    "    },\n",
    ")\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45cea9dc-84d6-4336-b97b-d07c72e5039e",
   "metadata": {},
   "source": [
    "## Query\n",
    "\n",
    "Now, let's start JupySQL, authenticate, and start querying the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "536942de-15b0-47c3-9d29-76714555f5c9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cd4a3a3f-3132-4386-87b5-2ca2c116afa1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%sql oracle+oracledb://ploomber_app:ploomber_app_password@localhost:1521/?service_name=FREEPDB1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f0cf0b7-c27b-4b20-b6b4-10edeaf9d91b",
   "metadata": {},
   "source": [
    "List the tables in the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1c9fc715-7dae-43a6-a695-31b787329d91",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Name</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>iris</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+------+\n",
       "| Name |\n",
       "+------+\n",
       "| iris |\n",
       "+------+"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sqlcmd tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7ba0f68-5e44-4879-a051-3ba4e61d0642",
   "metadata": {},
   "source": [
    "Query some data in iris table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "68d9ab32-dab5-45e9-8093-a99e933430eb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=FREEPDB1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sepal_length</th>\n",
       "            <th>sepal_width</th>\n",
       "            <th>petal_length</th>\n",
       "            <th>petal_width</th>\n",
       "            <th>species</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6.7</td>\n",
       "            <td>2.5</td>\n",
       "            <td>5.8</td>\n",
       "            <td>1.8</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7.2</td>\n",
       "            <td>3.6</td>\n",
       "            <td>6.1</td>\n",
       "            <td>2.5</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.5</td>\n",
       "            <td>3.2</td>\n",
       "            <td>5.1</td>\n",
       "            <td>2.0</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.4</td>\n",
       "            <td>2.7</td>\n",
       "            <td>5.3</td>\n",
       "            <td>1.9</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.8</td>\n",
       "            <td>3.0</td>\n",
       "            <td>5.5</td>\n",
       "            <td>2.1</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------------+-------------+--------------+-------------+-----------+\n",
       "| sepal_length | sepal_width | petal_length | petal_width |  species  |\n",
       "+--------------+-------------+--------------+-------------+-----------+\n",
       "|     6.7      |     2.5     |     5.8      |     1.8     | virginica |\n",
       "|     7.2      |     3.6     |     6.1      |     2.5     | virginica |\n",
       "|     6.5      |     3.2     |     5.1      |     2.0     | virginica |\n",
       "|     6.4      |     2.7     |     5.3      |     1.9     | virginica |\n",
       "|     6.8      |     3.0     |     5.5      |     2.1     | virginica |\n",
       "+--------------+-------------+--------------+-------------+-----------+"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM iris FETCH NEXT 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "930552ed-c9d3-4e89-99d7-5e2c42bd3f28",
   "metadata": {},
   "source": [
    "Query our data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8b58af21-cec8-4ebc-bfa5-be5a58d31f07",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=FREEPDB1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNT(*)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>150</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------+\n",
       "| COUNT(*) |\n",
       "+----------+\n",
       "|   150    |\n",
       "+----------+"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM iris"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "354002fa-2ee8-4667-8c18-d650058cca62",
   "metadata": {},
   "source": [
    "## Parametrize queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "497e68aa-2acd-4642-aed7-b7014a27875b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "threshold = 5.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3e713450-67a4-4142-8876-0eca4deca5af",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=FREEPDB1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNT(*)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>22</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----------+\n",
       "| COUNT(*) |\n",
       "+----------+\n",
       "|    22    |\n",
       "+----------+"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM iris\n",
    "WHERE sepal_length < {{threshold}}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7499ae1d-6ad6-4eb3-80d1-0dbad8bd69c3",
   "metadata": {},
   "source": [
    "## CTEs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f3ade8af-05e3-4ea4-bf0e-461406161064",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=FREEPDB1\n",
      "Skipping execution...\n"
     ]
    }
   ],
   "source": [
    "%%sql --save saved_cte --no-execute\n",
    "SELECT * FROM iris\n",
    "WHERE sepal_length > 6.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "33f66588-5f52-478a-b077-dc1387b4f50d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "*  oracle+oracledb://ploomber_app:***@localhost:1521/?service_name=FREEPDB1\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sepal_length</th>\n",
       "            <th>sepal_width</th>\n",
       "            <th>petal_length</th>\n",
       "            <th>petal_width</th>\n",
       "            <th>species</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6.7</td>\n",
       "            <td>2.5</td>\n",
       "            <td>5.8</td>\n",
       "            <td>1.8</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7.2</td>\n",
       "            <td>3.6</td>\n",
       "            <td>6.1</td>\n",
       "            <td>2.5</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.5</td>\n",
       "            <td>3.2</td>\n",
       "            <td>5.1</td>\n",
       "            <td>2.0</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.4</td>\n",
       "            <td>2.7</td>\n",
       "            <td>5.3</td>\n",
       "            <td>1.9</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6.8</td>\n",
       "            <td>3.0</td>\n",
       "            <td>5.5</td>\n",
       "            <td>2.1</td>\n",
       "            <td>virginica</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------------+-------------+--------------+-------------+-----------+\n",
       "| sepal_length | sepal_width | petal_length | petal_width |  species  |\n",
       "+--------------+-------------+--------------+-------------+-----------+\n",
       "|     6.7      |     2.5     |     5.8      |     1.8     | virginica |\n",
       "|     7.2      |     3.6     |     6.1      |     2.5     | virginica |\n",
       "|     6.5      |     3.2     |     5.1      |     2.0     | virginica |\n",
       "|     6.4      |     2.7     |     5.3      |     1.9     | virginica |\n",
       "|     6.8      |     3.0     |     5.5      |     2.1     | virginica |\n",
       "+--------------+-------------+--------------+-------------+-----------+"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql --with saved_cte SELECT * FROM saved_cte FETCH NEXT 5 ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "055043c2-7590-4c8c-a74d-49d27f0f2865",
   "metadata": {},
   "source": [
    "This is what JupySQL executes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ce01a806-133f-4b9d-b7dc-ffe3ad00e54c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WITH\n",
      "SELECT * FROM iris\n",
      "WHERE sepal_length > 6.0\n"
     ]
    }
   ],
   "source": [
    "query = %sqlcmd snippets saved_cte\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb5dcc49-4af9-40ad-a3e2-4ae83d223200",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "\n",
    "To stop and remove the container:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0817041d-25aa-4caa-b62e-eef3cf2c5aa0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONTAINER ID   IMAGE              COMMAND                  CREATED         STATUS         PORTS                                       NAMES\n",
      "cc531fdc8802   gvenzl/oracle-free   \"container-entrypoin…\"   7 minutes ago   Up 7 minutes   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp   oracle\n"
     ]
    }
   ],
   "source": [
    "! docker container ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7f462c66-8f11-49c3-b32c-8323a1fd7043",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture out\n",
    "! docker container ls --filter name=oracle --quiet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "2a319a89-49df-47ef-af29-19377b39af66",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Container id: cc531fdc8802\n"
     ]
    }
   ],
   "source": [
    "container_id = out.stdout.strip()\n",
    "print(f\"Container id: {container_id}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19d7df0b-7d3b-4da5-9c2e-7e4e1cb9c7de",
   "metadata": {},
   "source": [
    "Remove the container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "2ec3464e-a108-4710-aa84-14f138e60c25",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cc531fdc8802\n",
      "cc531fdc8802\n"
     ]
    }
   ],
   "source": [
    "! docker container stop {container_id}\n",
    "! docker container rm {container_id}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7ab5d851-735f-44eb-8d59-57b3b1149c13",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES\n"
     ]
    }
   ],
   "source": [
    "! docker container ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d0d7fec-c32e-489c-bdfa-5a5832f4c7ab",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  },
  "myst": {
   "html_meta": {
    "description lang=en": "Query a Oracle database from Jupyter via JupySQL",
    "keywords": "jupyter, sql, jupysql, postgres",
    "property=og:locale": "en_US"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
